﻿#encoding: utf-8
#this file can be used in other ruby file by loading 'wechat_api.rb'

require 'rest-client'   #to use RestClient
require 'json'          #to use JSON
require 'awesome_print' #to use ap helper

load 'wechat_api.rb'
load 'rds_api.rb'
@rds = @rds_tt

#上传数据，如果成功返回新插入纪录号，失败则返回新插入纪录号的负值
def uploadOrderListToErp list = [], drill = false

    return 0 if list.size == 0

    url = 'http://tapi.tutenglife.com/Api/Erp/OrdersUpload'
    url = 'https://mall.totembio.cn/api/erp/order/sync' if drill == true

    sql = "insert into tutem_mall.eb_erp_sync(client,method,url,data,request_time) values(
        'OMS2','post','#{@rds.escape url}','#{@rds.escape list.to_json}', #{Time.now.to_i})"
    queryRds sql

    eid = 0
    # bard说的，不确定真假
    # the LAST_INSERT_ID() function is maintained on a per-connection basis.
    # This means that each connection has its own independent LAST_INSERT_ID() value.
    (queryRds('select last_insert_id() as lii')).each { |i| eid = i['lii'] }

    begin
        res = RestClient.post url, list.to_json, :content_type => 'application/json'
        #res = RestClient.post url, list, :content_type => 'application/json'
    rescue=>e
        puts "[#{Time.now.strftime('%Y-%m-%d %H:%M:%S')}] post error"
        puts "  url: #{url}"
        puts "  error: #{e.to_s}"
        puts "  data: #{list.to_json}"
        res_code = e.to_s.split(' ').first.to_i
        res_body = e.to_s
        sql = "update tutem_mall.eb_erp_sync set response_time = #{Time.now.to_i}, code = #{res_code}, response = '#{@rds.escape res_body}' where id = #{eid}"
        queryRds sql
        if res_code != 200
            report = "【订单上传失败】吴婧请联系郭松手工处理：#{res_body}"
            send_message_to_bot 'WWBOT_TT', report, ['@all']
        end
        return -1 * eid
    end

    puts "post list to #{url}: #{res.code}"

    sql = "update tutem_mall.eb_erp_sync set response_time = #{Time.now.to_i}, code = #{res.code}, response = '#{@rds.escape res.body}' where id = #{eid}"
    queryRds sql

    return eid

end

=begin
esl = {
    'TTYSY0001-01'=> {'name'=> '图腾益生口服液30支装', 'unit'=> '单盒', 'sku_id'=> 'TTYSY0001-01'},
    'TTYSJDT0001-01'=> {'name'=> '图腾益生液冻干粉剂', 'unit'=> '单盒', 'sku_id'=> 'TTYSJDT0001-01'},
    'TTYSJDT0001-03'=> {'name'=> '图腾益生液冻干粉剂', 'unit'=> '三盒装(限时补贴)', 'sku_id'=> 'TTYSJDT0001-03'},
    'HSCT0001-01'=> {'name'=> '海参肠肽复合益生菌粉', 'unit'=> '单盒-30', 'sku_id'=> 'HSCT0001-01'},
    'HSCT0001-02'=> {'name'=> '海参肠肽复合益生菌粉', 'unit'=> '两盒-80', 'sku_id'=> 'HSCT0001-02'},
    'HSCT0001-04'=> {'name'=> '海参肠肽复合益生菌粉', 'unit'=> '四盒-200', 'sku_id'=> 'HSCT0001-04'},
    'TTYX0001-01'=> {'name'=> '图腾益仙冻干粉剂', 'unit'=> '单盒', 'sku_id'=> 'TTYX0001-01'},
    'TTYX0001-03'=> {'name'=> '图腾益仙冻干粉剂', 'unit'=> '三盒装(限时补贴)', 'sku_id'=> 'TTYX0001-03'},
    'FZY0001-01'=> {'name'=> '图腾风之友固体饮料', 'unit'=> '单盒', 'sku_id'=> 'FZY0001-01'},
    'FZY0001-03'=> {'name'=> '图腾风之友固体饮料', 'unit'=> '三盒装', 'sku_id'=> 'FZY0001-03'},
    'YBD0001-01'=> {'name'=> '图腾益贝多固体饮料', 'unit'=> '单盒', 'sku_id'=> 'YBD0001-01'},
    'YBD0001-03'=> {'name'=> '图腾益贝多固体饮料', 'unit'=> '三盒装', 'sku_id'=> 'YBD0001-03'},
    'PT0001-01'=> {'name'=> '脾肽臻品小牛脾口服生物制剂', 'unit'=> '默认', 'sku_id'=> 'PT0001-01'},
    'TTYMJ0001-01'=> {'name'=> '图腾益美玑冻干粉剂', 'unit'=> '单盒', 'sku_id'=> 'TTYMJ0001-01'},
    'TTYMJ0001-03'=> {'name'=> '图腾益美玑冻干粉剂', 'unit'=> '三盒装(限时送2盒脾肽)', 'sku_id'=> 'TTYMJ0001-03'},
    'LHJ0001-01'=> {'name'=> '硫磺菌人参饮药食同源专利真菌', 'unit'=> '单盒', 'sku_id'=> 'LHJ0001-01'},
    'XES0003-01'=> {'name'=> '小儿拾益生菌固体饮料', 'unit'=> '单盒', 'sku_id'=> 'XES0003-01'},
    'DDT0001-01'=> {'name'=> '益生菌大豆肽固体饮料', 'unit'=> '单盒', 'sku_id'=> 'DDT0001-01'},
    'YBM0001-01'=> {'name'=> '图腾益贝敏固体饮料', 'unit'=> '单盒', 'sku_id'=> 'YBM0001-01'},
    'YBM0001-03'=> {'name'=> '图腾益贝敏固体饮料', 'unit'=> '三盒装', 'sku_id'=> 'YBM0001-03'},
    'ZJY0001-01'=> {'name'=> '植精元放化疗术后专用补充营养蛋白粉', 'unit'=> '默认', 'sku_id'=> 'ZJY0001-01'},
    'YSZJ0001-01'=> {'name'=> '益生至简益生菌复合固体饮料', 'unit'=> '默认', 'sku_id'=> 'YSZJ0001-01'},
    'QEZJ0001-01'=> {'name'=> '轻而益简益生菌复合固体饮料', 'unit'=> '默认', 'sku_id'=> 'QEZJ0001-01'}
}
=end
def erpSkuList
    return @erpSkuList if @erpSkuList
    @erpSkuList = {}
    sql = "select * from tutem_mall.eb_unify_pcode"
    queryRds(sql).each do |q|
        pav_unique = q['pav_unique']
        name = q['product_name']
        unit = q['pav_sku']
        sku_id = q['erpcode']
        if ( sku_id != pav_unique )
            #目前在eb_unify_pcode中允许erpcode重复，这样最后的值将保留
            @erpSkuList.store sku_id, { 'name' => name, 'unit' => unit, 'sku_id' => sku_id }
        end
    end
    return @erpSkuList
end

=begin

对于PPPP-n的形式一律保留为PPPP去建立mapping
但对于PPPP-n-SSSS这种带有促销代码的，完全保留键值

=end
def defaultErpCodeMappingByPcode
    return @decMapping if @decMapping
    @decMapping = {}
    sql = "select * from tutem_mall.eb_unify_pcode"
    queryRds(sql).each do |q|
        pav_unique = q['pav_unique']
        pav_barcode = q['pav_barcode']
        erpcode = q['erpcode']
        if (pav_barcode.split('-').last == '1' && erpcode != pav_unique )
            #erpcode split 是为了去掉那些没有erpcode只用了attr uniq的
            pcode = pav_barcode.split('-').first
            @decMapping.store pcode, erpcode
        else
            if pav_barcode.split('-').size == 3 #但对于PPPP-n-SSSS的促销用代码则予以完全保留
                pcode = pav_barcode
                @decMapping.store pcode, erpcode
            end
        end
    end
    return @decMapping
end

def getErpSkuIdByStandardCode scode
    mapping = defaultErpCodeMappingByPcode
    mapped = mapping[scode]
    return mapped
end

def getErpSku scode
    sku_id = getErpSkuIdByStandardCode scode
    sku = erpSkuList[sku_id.to_s] 
    return sku
end

def getUploadDataForOrder order

    user_address = order['user_address']
    addrs = user_address.split(' ')
    state = addrs[0].to_s
    city = addrs[1].to_s
    district = addrs[2].to_s

    if order['shipping_type' ] == 2
        user_address = '自提或补拍'
        state = '同前'
        city = '同前'
        district = '同前'
    end

    payment = 'Tupay'
    payment = 'WechatPay' if order['pay_type'] == 'weixin'

    goods = JSON.parse order['goods_info']
    items = []
    goods.each do |good|
        shop_sku_code = good['pcode']
        qty = good['standard_qty']
        if good['attr_barcode'].split('-').size == 3 #当原始的pav code为PPPP-nn-yymmdd形式时，保留原始产品代码及购物车数量
            shop_sku_code = good['attr_barcode']
            qty = good['cart_num']
        end
        erp_sku = getErpSku shop_sku_code
        if erp_sku.nil?
            puts "skip uploading order ##{sprintf('%04d',order['id'])} cause #{good['pcode']} not mapped"
            return {} 
        end
        item = {
            'sku_id': erp_sku['sku_id'],
            'shop_sku_id': shop_sku_code,
            'amount': good['weixin_paid'] + good['tupay_paid'],
            'base_price': 0.0,
            'qty': qty,
            'name': erp_sku['name'],
            'outer_oi_id': good['product_id'].to_s + "-" + good['attr_unique'].to_s,
            'batch_id': '0',
            'produced_date':  '2033-03-03'
        }
        items += [item]
    end

    data = {
        "shop_id"=>              13667520,
        "so_id"=>                'TORDER-' + order['id'].to_s,
        "order_date"=>           Time.at(order['pay_time']).strftime('%Y-%m-%d %H:%M:%S'),
        "shop_status"=>          "WAIT_SELLER_SEND_GOODS",
        "shop_buyer_id"=>        'TUSER-' + order['uid'].to_s,
        "receiver_country"=>     'CN',
        "receiver_state"=>       state,
        "receiver_city"=>        city,
        "receiver_district"=>    district,
        "receiver_address"=>     user_address,
        "receiver_name"=>        order['user_name'],
        "receiver_phone"=>       order['user_phone'],
        "receiver_zip"=>         "123456",
        "pay_amount"=>           order['pay_price'].to_f.round(2), #order['total_price'].to_f.round(2),
        "freight"=>              order['total_postage'].to_f.round(2),
        "remark"=>               order['remark'].to_s,
        "buyer_message"=>        order['mark'],
        "shop_modified"=>        Time.now.strftime('%Y-%m-%d %H:%M:%S'),
        "is_cod"=>               false,              #是否货到付款
        "l_id"=>                 order['delivery_id'].to_s,
        "logistics_company"=>    order['delivery_name'].to_s,
        "lc_id"=>                "",
        "labels"=>               "",
        "items"=>                items,
        "pay"=>{
            "outer_pay_id"=>     order['pay_id'],
            "pay_date"=>         Time.at(order['pay_time']).strftime('%Y-%m-%d %H:%M:%S'),
            "payment"=>          payment,
            "seller_account"=>   "TOTEMBIO",
            "buyer_account"=>    "TUSER-" + order['uid'].to_s,
            "amount"=>           order['pay_price'].to_f.round(2)
        }
    }

    return data

end

def test_sync
    data2 = {
                   "ts" => Time.now.to_i,
            "partnerid" => "erp",
               "method" => "logistics.upload",
                 "sign" => "61bdde90f107fe2902db0e829bb0c790",
                 "o_id" => "24918545",
                 "l_id" => "CS0000000001",
                "so_id" => "TORDER-7147",
                "lc_id" => "STO",
           "order_from" => "open",
            "wms_co_id" => "11886308",
    "logistics_company" => "申通E物流",
            "send_date" => "2022-12-15 20:21:36.065",
                "items" => [  { "oi_id" => "29806480", "sku_id" => "TTYX0001-01", "qty" => 3, "name" => "图腾益仙冻干粉剂", "outer_oi_id" => "1015-0cd5765d", "so_id" => "TORDER-7147" } ]
    }

    url = 'https://mall.totembio.cn/api/erp/order/sync'
    res = RestClient.post url, data2.to_json, :content_type => 'application/json'
    puts "post data2 to #{url}: #{res.code}"
end

def setErpSync
    sql = "update tutem_mall.eb_store_order set erp_sync = 1
    where status = 0 and refund_status = 0 and paid = 1 and erp_sync = 0 and id in (select oid from tutem_mall.eb_pickup_book where temp_id = 5) and pay_time >= 1671984000;"
    #where status = 0 and refund_status = 0 and paid = 1 and erp_sync = 0 and id in (select oid from tutem_mall.eb_pickup_book where temp_id = 5) and id > 7891 and uid = 1;"
    queryRds sql
end

def getUploadDataForOid order_id
    sql = "select o.id as id, o.uid, o.erp_sync, o.erp_upload_id, o.erp_download_id,
            o.real_name as user_name, o.user_phone, o.user_address,
            o.shipping_type, o.delivery_type, o.delivery_id, o.delivery_type, o.delivery_name,
            o.total_price, o.total_postage, o.pay_time, o.pay_price, o.pay_type, o.order_id as pay_id,
            o.status, o.refund_status, o.mark, o.remark,
            p.goods_info, p.pcode_info
        from tutem_mall.eb_pickup_book p left join tutem_mall.eb_store_order o on (p.oid = o.id )
        where o.id = #{order_id}"
    items = []
    (queryRds(sql)).each do |order|
        item = getUploadDataForOrder order
        items += [item]
    end
    return items
end

def uploadNewOrders

    sql = "select o.id as id, o.uid, o.erp_sync, o.erp_upload_id, o.erp_download_id,
            o.real_name as user_name, o.user_phone, o.user_address,
            o.shipping_type, o.delivery_type, o.delivery_id, o.delivery_type, o.delivery_name,
            o.total_price, o.total_postage, o.pay_time, o.pay_price, o.pay_type, o.order_id as pay_id,
            o.status, o.refund_status, o.mark, o.remark,
            p.goods_info, p.pcode_info
        from tutem_mall.eb_pickup_book p left join tutem_mall.eb_store_order o on (p.oid = o.id )
        where
            o.status = 0 and o.refund_status = 0 and o.paid = 1 and o.delivery_type is NULL and o.erp_sync = 1 and o.erp_upload_id = 0 limit 30"

    list = []
    ids = []
    failed_ids = []
    (queryRds(sql)).each do |order|
        item = getUploadDataForOrder order
        if item == {} #如果有出错的将会跳过
            failed_ids += [order['id']]
            next
        end
        ids += [order['id']]
        list += [item]
    end

    if failed_ids.size > 0
        sql = "update tutem_mall.eb_store_order set erp_upload_id = -999999 where id in (#{failed_ids.join(',')})"
        queryRds sql
        puts sql
    end

    if ids.size > 0 
        is_drill = false
        eid = uploadOrderListToErp list, is_drill
        sql = "update tutem_mall.eb_store_order set erp_upload_id = #{eid} where id in (#{ids.join(',')})"
        puts sql
        queryRds sql
    end

end

def uploadOrdersToErp

    #将所有可上传erp的订单的erp_sync标识设置为1
    setErpSync
    uploadNewOrders

end

def confirmOrderShipment order_id, delivery_name, delivery_id
    url = 'https://mall.totembio.cn/api/confirm_delivery'
    data = { 'order_id' => order_id, 'delivery_name' => delivery_name, 'delivery_id' => delivery_id, 'admin_id' => 0, 'api_key'=>ENV['PSI_PASSWORD'] }
    begin
        res = RestClient.post url, data.to_json, :content_type => 'application/json'
    rescue=>e
        return false
    end
    ap res.body
    return true
end

def downloadErpDelivery

    #针对所有已设置erp_sync==1,erp_upload_id>0然而erp_download_id==0,且status==0的订单
    #在eb_erp_sync中寻找相关纪录，合并items后与pickup中的商品做对比，如果数量对比完整，则将erp_download_id设置为最后一个id
    #并将status设为待收货,如果有多条发货编号则重写发货编号,快递公司名写"多张单号"
    #现在的主要风险是对方服务器重复发送同一订单数据,未来在服务器断通过erp_source_code和method结合来处理 TBO

    sql = "select o.id, p.goods_info from tutem_mall.eb_store_order o left join tutem_mall.eb_pickup_book p on (o.id = p.oid) where o.erp_sync = 1 and o.erp_upload_id > 0 and o.erp_download_id = 0 and o.status = 0 and o.order_code like '%FC%'"
    #sql = "select o.id, p.pcode_info, p.goods_info from tutem_mall.eb_store_order o left join tutem_mall.eb_pickup_book p on (o.id = p.oid) where o.id = 12267" #DEBUG-12267时打开，并注释掉上面
    orders = {}


    queryRds(sql).each do |q|

        pcodes = {}
        goods = JSON.parse q['goods_info']
        goods.each do |good|
            pcode = good['pcode']
            qty = good['standard_qty']
            if good['attr_barcode'].split('-').size == 3 #当原始的pav code为PPPP-nn-yymmdd形式时，保留原始产品代码及购物车数量
                pcode = good['attr_barcode']
                qty = good['cart_num']
            end
            pcodes.store pcode, qty
        end
        q.store 'pcode_info', pcodes #这里要重写，因为直接从pickup中出来的是基础单位统计

        pcodes_checksum = 0
        pcodes.each { |pcode, num| pcodes_checksum += num }
        q.store 'pcodes_checksum', pcodes_checksum
        orders.store q['id'], q #if q['id'] == 12267 #DEUBG-12267时打开if条件

    end

    orders.each do |order_id, order|

        sql = "select * from tutem_mall.eb_erp_sync where sync = #{order_id}"
        erp_orders = {} #需要排除同一ERP_ORDER_CODE
        last_erp_sync_id = 0
        queryRds(sql).each do |q|
            qty_subtotal = 0
            begin
                data = JSON.parse q['data']
                erp_order_code = data['o_id'].to_s #防止返回是个长整数
                items = data['items']
                items.each { |item| qty_subtotal += item['qty'] }
                erp_order_info = {}
                erp_order_info.store 'order_id', order_id
                erp_order_info.store 'erp_sync_id', q['id']
                erp_order_info.store 'erp_order_code', erp_order_code
                erp_order_info.store 'qty_subtotal', qty_subtotal
                erp_order_info.store 'delivery_name', data['logistics_company']
                erp_order_info.store 'delivery_id', data['l_id']
                erp_order_info.store 'send_date',data['send_date']
                erp_orders.store  erp_order_code, erp_order_info
                last_erp_sync_id = q['id']
            rescue=>e
            end
        end

        erp_qty_sum = 0
        delivery_name  = ''
        delivery_id = ''
        erp_orders.each do |eoc, erp_order_info|
            delivery_name = delivery_name + erp_order_info['delivery_name'] + ' '
            delivery_id = delivery_id + erp_order_info['delivery_id'] + ' '
            erp_qty_sum += erp_order_info['qty_subtotal']
        end
        delivery_name = delivery_name.rstrip
        delivery_id = delivery_id.rstrip
        if erp_qty_sum == order['pcodes_checksum']
            puts "confirmOrderShipment #{order_id}, '#{delivery_name}', '#{delivery_id}'"
            confirmOrderShipment order_id, delivery_name, delivery_id #DEUBG-12267时注释掉
            usql = "update tutem_mall.eb_store_order set erp_download_id = #{last_erp_sync_id} where id = #{order_id}"
            queryRds usql #DEBUG-12267时注释掉
        else
            puts "UNMATCHED: order #{order_id} checksum: #{order['pcodes_checksum']}, erp_qty_sum: #{erp_qty_sum}"
        end

    end

end

#手工强制完成ERP已发货标记
def forceErpDeliveryFinished order_id

        sql = "select * from tutem_mall.eb_erp_sync where sync = #{order_id}"
        erp_orders = {}
        last_erp_sync_id = 0
        queryRds(sql).each do |q|
            qty_subtotal = 0
            begin
                data = JSON.parse q['data']
                erp_order_code = data['o_id'].to_s #防止返回是个长整数
                items = data['items']
                items.each { |item| qty_subtotal += item['qty'] }
                erp_order_info = {}
                erp_order_info.store 'erp_sync_id', q['id']
                erp_order_info.store 'erp_order_code', erp_order_code
                erp_order_info.store 'qty_subtotal', qty_subtotal
                erp_order_info.store 'delivery_name', data['logistics_company']
                erp_order_info.store 'delivery_id', data['l_id']
                erp_order_info.store 'send_date',data['send_date']
                erp_orders.store  erp_order_code, erp_order_info
                last_erp_sync_id = q['id']
            rescue=>e
            end
        end

        erp_qty_sum = 0
        delivery_name  = ''
        delivery_id = ''
        erp_orders.each do |eoc, erp_order_info|
            delivery_name = delivery_name + erp_order_info['delivery_name'] + ' '
            delivery_id = delivery_id + erp_order_info['delivery_id'] + ' '
            erp_qty_sum += erp_order_info['qty_subtotal']
        end
        delivery_name = delivery_name.rstrip
        delivery_id = delivery_id.rstrip

        puts "confirmOrderShipment #{order_id}, '#{delivery_name}', '#{delivery_id}'"
        confirmOrderShipment order_id, delivery_name, delivery_id
        usql = "update tutem_mall.eb_store_order set erp_download_id = #{last_erp_sync_id} where id = #{order_id}"
        queryRds usql

end

#ap getUploadDataForOid 19428

#erp上传错误的订单，可以直接将erp_uploaded_id设置为0后手工运行本文件查看错误提示
downloadErpDelivery
uploadOrdersToErp
